-- @owner: zhao-pengcheng-g
-- @date: 2022/01/24
-- @testpoint:  after 多个触发事件，FOR EACH row

--step1:建表;expect:成功
drop table if exists table_src_0005_1 cascade;
drop table if exists table_des_0005_2 cascade;
CREATE TABLE table_src_0005_1(id1 INT, id2 INT, id3 INT) WITH (STORAGE_TYPE=USTORE)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);
CREATE TABLE table_des_0005_2(id1 INT, id2 INT, id3 INT) WITH (STORAGE_TYPE=USTORE)
partition by range(id2)
(
        partition p1 values less than(1),
        partition p2 values less than(100000),
        partition p3 values less than(maxvalue)
);

--step2:创建视图，创建函数，创建触发器;expect:成功
CREATE OR REPLACE FUNCTION AINSERT_FUNC_0005()
RETURNS TRIGGER AS $$
BEGIN
if(TG_OP='DELETE')
then
delete from table_des_0005_2 where id2=2;
RETURN NEW;
end if;
if(TG_OP='INSERT')
then
insert into table_des_0005_2 values(new.id1,new.id2,new.id3);
RETURN NEW;
end if;
if(TG_OP='UPDATE')
then
update table_des_0005_2 set id1=2000 where id2=5;
RETURN NEW;
end if;
END;
$$LANGUAGE plpgsql;
/
drop trigger if exists ATRIGGER_0005 on table_src_0005_1;
CREATE TRIGGER ATRIGGER_0005 after delete or insert or update ON table_src_0005_1
FOR EACH row
EXECUTE PROCEDURE AINSERT_FUNC_0005();
/
drop sequence if exists test_trigger_seq1_0005;
create sequence test_trigger_seq1_0005;
CREATE OR REPLACE FUNCTION BINSERT_FUNC_0005()
RETURNS TRIGGER AS $$
BEGIN
if(TG_OP='DELETE')
then
delete from table_des_0005_2 where id3=3;
RETURN NEW;
end if;
if(TG_OP='INSERT')
then
insert into table_des_0005_2 values(nextval('test_trigger_seq1_0005'),nextval('test_trigger_seq1_0005'),nextval('test_trigger_seq1_0005'));
RETURN NEW;
end if;
if(TG_OP='UPDATE')
then
update table_des_0005_2 set id1=3000;
RETURN NEW;
end if;
END;
$$LANGUAGE plpgsql;
/

drop trigger if exists BTRIGGER_0005 on table_src_0005_1;
CREATE TRIGGER BTRIGGER_0005 before delete or insert or update ON table_src_0005_1
FOR EACH row
EXECUTE PROCEDURE BINSERT_FUNC_0005();
/

alter trigger BTRIGGER_0005 ON table_src_0005_1 rename to AATRIGGER_0005;
alter trigger ATRIGGER_0005 ON table_src_0005_1 rename to BTRIGGER_0005;
alter trigger AATRIGGER_0005 ON table_src_0005_1 rename to ATRIGGER_0005;

--step3:插入、更新、删除数据，触发trigger，expect:成功
insert into table_src_0005_1 values (1,1,1),(2,2,2),(3,3,3);
select * from table_src_0005_1;
select * from table_des_0005_2;
update table_src_0005_1 set id1=100;
select * from table_src_0005_1;
select * from table_des_0005_2;
delete from table_src_0005_1 where id1=100;
select * from table_src_0005_1;
select * from table_des_0005_2;

--step4:删除名称不存在的trigger，expect:失败，合理报错
--drop trigger_带if exists，trigger_name不存在：成功，notice
drop trigger if exists ABCDTRIGGER on table_src_0005_1;
--drop trigger_不带if exists，trigger_name不存在：报错
drop trigger ABCDTRIGGER on table_src_0005_1;

--step5:环境清理;expect:成功
drop TRIGGER BTRIGGER_0005 on table_src_0005_1 cascade;
drop trigger ATRIGGER_0005 on table_src_0005_1 cascade;
drop FUNCTION AINSERT_FUNC_0005;
drop FUNCTION BINSERT_FUNC_0005;
drop table table_src_0005_1 cascade;
drop table table_des_0005_2 cascade;

